package cn.gxa.edu_adm.dao.impl;

import cn.gxa.edu_adm.bean.Student;
import cn.gxa.edu_adm.commn.model.ResultData;
import cn.gxa.edu_adm.dao.StudentDao;
import cn.gxa.edu_adm.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoImpl  implements StudentDao {
    @Override
    public List<Student> findByManyWhere(Integer page, Integer limit, Integer id,
                                         String name, String phone, String idCard,
                                         Integer claId, Integer state) {
        //参数集合
        List<Object>  params=new ArrayList<>();
        //String 面试题 怎么优化sql  1.不能写1=1 2.尽量查询不用 *写上明确的查询字段
        StringBuffer  sql=new StringBuffer("select s.*,c.claName    from  student  s left join classes   c" +
                "  on  s.claId=c.claId where 1=1 ");  //sql动态 拼接
        if(id!=null){
            sql.append(" and  s.stuId = ?");
            params.add(id);
        }
        //name  有值 where  name=?
        if(name!=null){
            sql.append(" and  s.stuName like ?");
            params.add("%"+name+"%");
        }
        // studentType  where  studentType=?
        if(phone!=null){
            sql.append(" and  s.stuPhone=?");
            params.add(phone);
        }
        if(idCard!=null){
            sql.append(" and  s.stuIdCard=?");
            params.add(idCard);
        }
        if(claId!=null){
            sql.append(" and  s.claId=?");
            params.add(claId);
        }
        if(state!=null){
            sql.append(" and  s.stuState=?");
            params.add(state);
        }
        //(page-1)*limit
        sql.append(" order by s.stuUpdateTime desc limit "+(page-1)*limit+","+limit);
        System.out.println("sql:"+sql);
        //查询数据库
        QueryRunner   queryRunner  =new QueryRunner(DruidUtils.getDruidDataSource());
        try {
            return   queryRunner.query(sql.toString(),new BeanListHandler<>(Student.class),params.toArray());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int deleteById(Integer id) {
        QueryRunner  queryRunner=new QueryRunner(DruidUtils.getDruidDataSource());
        String  sql="delete  from  student where stuId=?";
        try {
            return   queryRunner.update(sql,id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int deleteByIds(String arrId) {
        QueryRunner  queryRunner=new QueryRunner(DruidUtils.getDruidDataSource());
        String  sql="delete  from  student where stuId in ("+arrId+")";
        System.out.println(sql);
        try {
            return   queryRunner.update(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int update(Student student) {
        QueryRunner  queryRunner=new QueryRunner(DruidUtils.getDruidDataSource());
        String  sql="update  Student set stuName=?,stuSex=?,stuBirthday=?" +
                ",stuPhone=?,stuIdCard=?,claId=?,stuState=?,stuUpdateTime=now() " +
                "where  stuId=?";
        int  rows=0;
        try {
            rows=  queryRunner.update(sql,
                    student.getStuName(),
                    student.getStuSex(),
                    student.getStuBirthday(),
                    student.getStuPhone(),
                    student.getStuIdCard(),
                    student.getClaId(),
                    student.getStuState(),
                    student.getStuId());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rows;
    }

    @Override
    public int insert(Student student) {
        QueryRunner  queryRunner=new QueryRunner(DruidUtils.getDruidDataSource());
        String  sql="insert into Student(stuName,stuSex,stuBirthday,stuPhone,stuIdCard,claId,stuState) value(?,?,?,?,?,?,?)";
        try {
            return   queryRunner.update(sql,student.getStuName(),
                    student.getStuSex(),
                    student.getStuBirthday(),
                    student.getStuPhone(),
                    student.getStuIdCard(),
                    student.getClaId(),
                    student.getStuState());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public Long findByManyWhereCount(Integer id, String name, String phone, String idCard, Integer claId, Integer state) {
        //参数集合
        List<Object>  params=new ArrayList<>();
        //String 面试题 怎么优化sql  1.不能写1=1 2.尽量查询不用 *写上明确的查询字段
        StringBuffer  sql=new StringBuffer("select count(stuId)  from student  where 1=1");  //sql动态 拼接
        if(id!=null){
            sql.append(" and  stuId = ?");
            params.add(id);
        }
        //name  有值 where  name=?
        if(name!=null){
            sql.append(" and  stuName like ?");
            params.add("%"+name+"%");
        }
        // studentType  where  studentType=?
        if(phone!=null){
            sql.append(" and  stuPhone=?");
            params.add(phone);
        }
        if(idCard!=null){
            sql.append(" and  stuIdCard=?");
            params.add(idCard);
        }
        if(claId!=null){
            sql.append(" and  claId=?");
            params.add(claId);
        }
        if(state!=null){
            sql.append(" and  stuState=?");
            params.add(state);
        }
        System.out.println("sql:"+sql);
        //查询数据库
        QueryRunner   queryRunner  =new QueryRunner(DruidUtils.getDruidDataSource());
        try {
            Object  count=queryRunner.query(sql.toString(),new ArrayHandler(),params.toArray())[0];
            return   Long.parseLong(count.toString());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Student> findByManyWhere(Integer id, String name, String phone, String idCard, Integer claId, Integer state) {
        //参数集合
        List<Object>  params=new ArrayList<>();
        //String 面试题 怎么优化sql  1.不能写1=1 2.尽量查询不用 *写上明确的查询字段
        StringBuffer  sql=new StringBuffer("select s.*   from  student  s  where  1=1 ");  //sql动态 拼接
        if(id!=null){
            sql.append(" and  s.stuId = ?");
            params.add(id);
        }
        //name  有值 where  name=?
        if(name!=null){
            sql.append(" and  s.stuName like ?");
            params.add("%"+name+"%");
        }
        // studentType  where  studentType=?
        if(phone!=null){
            sql.append(" and  s.stuPhone=?");
            params.add(phone);
        }
        if(idCard!=null){
            sql.append(" and  s.stuIdCard=?");
            params.add(idCard);
        }
        if(claId!=null){
            sql.append(" and  s.claId=?");
            params.add(claId);
        }
        if(state!=null){
            sql.append(" and  s.stuState=?");
            params.add(state);
        }
        //(page-1)*limit
        sql.append(" order by s.stuUpdateTime desc ");
        System.out.println("sql:"+sql);
        //查询数据库
        QueryRunner   queryRunner  =new QueryRunner(DruidUtils.getDruidDataSource());
        try {
            return   queryRunner.query(sql.toString(),new BeanListHandler<>(Student.class),params.toArray());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int batchAddStudent(Object [][] params) {
        QueryRunner   queryRunner  =new QueryRunner(DruidUtils.getDruidDataSource());
        String  sql="insert into Student" +
                "(stuName,stuSex,stuBirthday,stuPhone,stuIdCard) value(?,?,?,?,?)";

        try {
            queryRunner.batch(sql,params);
            return  1;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public Object[] statisticsAge(String [] title) {//[2000, 1990, 1980, 1970, 1960, 其他]
        String sql="select  " +
                "sum(case when  year(s.stuBirthday)<"+(Integer.parseInt(title[0])+10)+" and  year(s.stuBirthday)>="+title[0]+"  then 1 else 0 end)as '00'," +
                "sum(case when  year(s.stuBirthday)<"+title[0]+" and  year(s.stuBirthday)>="+(title[1])+"  then 1 else 0 end)as '90'," +
                "sum(case when  year(s.stuBirthday)<"+(title[1])+" and  year(s.stuBirthday)>="+(title[2])+"  then 1 else 0 end)as '80'," +
                "sum(case when  year(s.stuBirthday)<"+(title[2])+" and  year(s.stuBirthday)>="+(title[3])+"  then 1 else 0 end)as '70'," +
                "sum(case when  year(s.stuBirthday)<"+(title[3])+"   then 1 else 0 end)as '60'," +
                "sum(case when  year(s.stuBirthday)>="+(Integer.parseInt(title[0])+10)+" or year(s.stuBirthday)is null  then 1 else 0  end)as 'other'" +
                "from  student s;";
        QueryRunner   queryRunner  =new QueryRunner(DruidUtils.getDruidDataSource());

        try {
            return  queryRunner.query(sql,new ArrayHandler());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return  null;
    }
}
